﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient

Namespace DB

    'マスタ編集
    Public Class MysqlUtilities
        Public Structure STC_MeisaiTable
            'SQL文
            Private m_sql As String
            '列を表示するかどうか示す
            Private m_visible As Boolean()
            '列のタイトル
            Private m_title As String()
            '列の幅
            Private m_width As Integer()

            Public Sub New(ByVal _sql As String, ByVal _visible As Boolean(), ByVal _title As String(), ByVal _width As Integer())
                Me.m_sql = _sql
                Me.m_visible = _visible
                Me.m_title = _title
                Me.m_width = _width
            End Sub

            Public Property Sql() As String
                Get
                    Return m_sql
                End Get
                Set(ByVal value As String)
                    m_sql = value
                End Set
            End Property

            '列を表示するかどうか示す
            Public Property Visible() As Boolean()
                Get
                    Return m_visible
                End Get
                Set(ByVal value As Boolean())
                    m_visible = value
                End Set
            End Property
            '列のタイトル
            Public Property Title() As String()
                Get
                    Return m_title
                End Get
                Set(ByVal value As String())
                    m_title = value
                End Set
            End Property
            '列の幅
            Public Property Width() As Integer()
                Get
                    Return m_width
                End Get
                Set(ByVal value As Integer())
                    m_width = value
                End Set
            End Property
        End Structure

        Public Structure STC_Item
            '列ID
            Private m_itemId As String
            '列名
            Private m_itemName As String
            '列のデータ型
            Private m_type As MySqlDbType
            '列のサイズ
            Private m_size As Integer

            '値とのオペレータ
            Private m_operat As String
            'パラメーターとのオペレータ
            Private m_sqlKey As String

            'パラメーター内容
            Private m_paramValue As String

            Public Sub New(ByVal _itemId As String, ByVal _itemName As String, ByVal _type As MySqlDbType, ByVal _size As Integer, ByVal _sqlKey As String, ByVal _operat As String, _
             ByVal _paramValue As String)
                Me.m_itemId = _itemId
                Me.m_itemName = _itemName
                Me.m_type = _type
                Me.m_size = _size
                Me.m_operat = _operat
                Me.m_sqlKey = _sqlKey
                Me.m_paramValue = _paramValue
            End Sub

            '列ID
            Public Property ItemId() As String
                Get
                    Return " " & m_itemId & Operat & "@" & m_itemId.ToUpper()
                End Get
                Set(ByVal value As String)
                    m_itemId = value
                End Set
            End Property

            '列名
            Public Property ItemName() As String
                Get
                    Return m_itemName
                End Get
                Set(ByVal value As String)
                    m_itemName = value
                End Set
            End Property
            '列のデータ型
            Public Property Type() As MySqlDbType
                Get
                    Return m_type
                End Get
                Set(ByVal value As MySqlDbType)
                    m_type = value
                End Set
            End Property
            '列のサイズ
            Public Property Size() As Integer
                Get
                    Return m_size
                End Get
                Set(ByVal value As Integer)
                    m_size = value
                End Set
            End Property

            '値とのオペレータ
            Public Property Operat() As String
                Get
                    Return " " & m_operat & " "
                End Get
                Set(ByVal value As String)
                    m_operat = value
                End Set
            End Property

            'パラメーターとのオペレータ
            Public Property SqlKey() As String
                Get
                    Return " " & m_sqlKey & " "
                End Get
                Set(ByVal value As String)
                    m_sqlKey = value
                End Set
            End Property

            'パラメーターID
            Public ReadOnly Property ParamId() As String
                Get
                    Return "@" & m_itemId.ToUpper()
                End Get
            End Property

            'パラメーター内容
            Public Property ParamValue() As String
                Get
                    Return m_paramValue
                End Get
                Set(ByVal value As String)
                    m_paramValue = value
                End Set
            End Property
        End Structure

        'SQL文
        Private Const SQL_LIKE_A As String = "'%'+####+'%'"
        'LIKE ALL
        Private Const SQL_LIKE_S As String = "'%'+####"
        'LIKE START
        Private Const SQL_LIKE_E As String = "####+'%'"
        'LIKE END

#Region "帳票印刷"


#End Region

#Region "変換データ入力、データ変換、帳票印刷"
        '変換データ入力-[業務種別選択]画面用
        Public ReadOnly GYOMU As New STC_MeisaiTable("SELECT * FROM GYOMU ORDER by 業務種別", Nothing, Nothing, Nothing)

        '帳票印刷-[ＪＯＢ一覧]画面用
        'データ変換-[ＪＯＢ一覧]画面用
        Public ReadOnly CUSTOMER As New STC_MeisaiTable("SELECT * FROM CUSTOMER", Nothing, Nothing, Nothing)

        '帳票印刷-[お客様情報再編集]画面用
        Public ReadOnly JOBID As New STC_MeisaiTable("SELECT * FROM JOBID", Nothing, Nothing, Nothing)

#End Region

#Region "マスタ編集"
        'マスタ編集-[マスタ選択]画面用
        Public ReadOnly MST As New STC_MeisaiTable("SELECT MSTID,MSTNAME FROM tblMaster", New Boolean(1) {False, True}, New String(1) {"No.", "マスタ名"}, New Integer(1) {50, 250})

        Public MSTWhere As STC_Item() = New STC_Item(1) {New STC_Item("MSTID", "No.", MySqlDbType.VarChar, 3, "WHERE", "=", _
         Nothing), New STC_Item("MSTNAME", "マスタ名", MySqlDbType.VarChar, 50, "AND", "=", _
         Nothing)}

#End Region

#Region "ファイルリスト"

        ''' <summary>
        ''' dat_fileroot
        ''' </summary>
        ''' <remarks></remarks>
        Public SQL_FileRoot_select As String = "select * from dat_fileroot "


        ''' <summary>
        ''' dat_file
        ''' </summary>
        ''' <remarks></remarks>
        Public SQL_File_update As String = "update dat_file set name = @V2, type = @V3, size = @V4, dateCreated = @V5, dateLastModified = @V6, dateLastAccessed = @V7 where id = @V1 "
       
        Public SQL_File_update_Value As STC_Item() = New STC_Item(6) { _
            New STC_Item("V1", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V3", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V4", "", MySqlDbType.Int64, 0, "", "", Nothing), _
            New STC_Item("V5", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V6", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V7", "", MySqlDbType.DateTime, 0, "", "", Nothing)}

        ''' <summary>
        ''' dat_file
        ''' </summary>
        ''' <remarks></remarks>
        Public SQL_Folder_update As String = "update dat_folder set name = @V2, dateCreated = @V5, dateLastModified = @V6, dateLastAccessed = @V7 where id = @V1 "
    
        Public SQL_Folder_update_Value As STC_Item() = New STC_Item(4) { _
            New STC_Item("V1", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V5", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V6", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V7", "", MySqlDbType.DateTime, 0, "", "", Nothing)}

        ''' <summary>
        ''' viw_filepath
        ''' </summary>
        ''' <remarks></remarks>
        Public ReadOnly DAT_File As New STC_MeisaiTable("SELECT id,path FROM viw_filepath", New Boolean(1) {True, True}, New String(1) {"No.", "マスタ名"}, New Integer(1) {50, 250})

        Public DAT_File_Where As STC_Item() = New STC_Item(1) { _
            New STC_Item("id", "No.", MySqlDbType.VarChar, 64, "WHERE", "=", Nothing), _
            New STC_Item("name", "マスタ名", MySqlDbType.VarChar, 256, "AND", "=", Nothing)}

        ''' <summary>
        ''' viw_folderpath
        ''' </summary>
        ''' <remarks></remarks>
        Public ReadOnly DAT_FolderPath As New STC_MeisaiTable("SELECT id,path FROM viw_folderpath", New Boolean(1) {True, True}, New String(1) {"No.", "マスタ名"}, New Integer(1) {50, 250})

        Public DAT_FolderPath_Where As STC_Item() = New STC_Item(1) { _
            New STC_Item("id", "No.", MySqlDbType.VarChar, 64, "WHERE", "=", Nothing), _
            New STC_Item("path", "path名", MySqlDbType.VarChar, 1000, "AND", "=", Nothing)}

        ''' <summary>
        ''' dat_fileroot
        ''' </summary>
        ''' <remarks></remarks>
        Public SQL_FileRoot_Insert As String = "insert into dat_fileroot (id,targetId, computerName, path, dateCreated, dateLastModified, dateLastAccessed) values (@V1,@V2,@V3,@V4,@V5,@V6,@V7)"
        Public SQL_FileRoot_Insert_Value As STC_Item() = New STC_Item(6) { _
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V3", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V4", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V5", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V6", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V7", "", MySqlDbType.DateTime, 0, "", "", Nothing)}

        Public SQL_FileRoot_Update As String = "update dat_fileroot set targetId = @V1, path=@V2 where id = @V3"
        Public SQL_FileRoot_Update_Value As STC_Item() = New STC_Item(2) { _
            New STC_Item("V1", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V3", "", MySqlDbType.Int16, 0, "", "", Nothing)}

        Public SQL_FileRoot_delete As String = "delete from dat_fileroot where id = @V1"
        Public SQL_FileRoot_delete_Value As STC_Item() = New STC_Item(0) {
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing)}

        Public SQL_file_delete As String = "DELETE from dat_file   WHERE id = @V1 OR left(id,Length(@V1) + 1) = @V1 + '-'"
        Public SQL_folder_delete As String = "DELETE from dat_folder WHERE id = @V1 OR left(id,Length(@V1) + 1) = @V1 + '_'"
        Public SQL_file_delete_Value As STC_Item() = New STC_Item(0) {
            New STC_Item("V1", "", MySqlDbType.String, 0, "", "", Nothing)}


#End Region


#Region "iMailHelper"

        'FIELDS TERMINATED BY ',' はデータが , で区切られているという意味。
        'ENCLOSED BY '"' は各データが " で囲われているという意味。
        Public SQL_import_csv As String = "LOAD DATA INFILE @V1 into table {0} character set utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES TERMINATED BY '\r\n';"
        Public SQL_import_csv_value As STC_Item() = New STC_Item(0) { _
            New STC_Item("V1", "", MySqlDbType.String, 0, "", "", Nothing)}

        Public SQL_select_viw_mail_account As String = "select * from viw_mail_account where flg=1 "
        Public SQL_update_mst_mail_account As String = "update mst_mail_account set password = @V2 where id = @V1 "
        Public SQL_update_mst_mail_account_value As STC_Item() = New STC_Item(1) { _
            New STC_Item("V1", "", MySqlDbType.Int64, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing)}

        Public SQL_select_dat_mail_uid As String = "select * from dat_mail_uid where accountId=@V1 "
        Public SQL_select_dat_mail_uid_value As STC_Item() = New STC_Item(0) { _
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing)}


        ''' <summary>
        ''' dat_mail_info
        ''' </summary>
        ''' <remarks></remarks>
        Public SQL_insert_dat_mail_info As String = "INSERT INTO dat_mail_info (accountId, uid, subject, from1, to1, cc, bcc, dateSent, body, attachments) VALUES (@V1, @V2, @V3, @V4, @V5, @V6, @V7, @V8, @V9, @V10) "
        Public SQL_insert_dat_mail_info_Value As STC_Item() = New STC_Item(9) { _
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing), _
            New STC_Item("V2", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V3", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V4", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V5", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V6", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V7", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("V8", "", MySqlDbType.DateTime, 0, "", "", Nothing), _
            New STC_Item("V9", "", MySqlDbType.Text, 0, "", "", Nothing), _
            New STC_Item("V10", "", MySqlDbType.String, 0, "", "", Nothing)}

        Public SQL_select_mst_mail_filter As String = "select * from mst_mail_filter where accountId=0 || accountId=@V1 "
        Public SQL_select_mst_mail_filter_value As STC_Item() = New STC_Item(0) { _
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing)}

        Public SQL_select_mst_filter As String = "select * from mst_filter where labelId in (@V1 ) "
        Public SQL_select_mst_filter_value As STC_Item() = New STC_Item(0) { _
            New STC_Item("V1", "", MySqlDbType.Int16, 0, "", "", Nothing)}


#End Region

#Region "プロシシャ"
        Public PRC_Folder_File_GetId As String = "prc_getFolderOrFileId"
        Public PRC_Folder_File_Path_Value As STC_Item() = New STC_Item(1) {
            New STC_Item("arg_path", "", MySqlDbType.String, 0, "", "", Nothing), _
            New STC_Item("arg_kubun", "", MySqlDbType.String, 0, "", "", Nothing)}

        Public PRC_Folder_File_Delete As String = "fun_deleteFolderOrFileByPath"
        Public PRC_Folder_File_Delete_Value As STC_Item() = New STC_Item(0) {
            New STC_Item("arg_path", "", MySqlDbType.String, 0, "", "", Nothing)}


#End Region

        Public Function GetParam(ByVal arrItem As STC_Item()) As MySqlParameter()
            Dim cnt As Integer = arrItem.Length
            Dim param As MySqlParameter() = New MySqlParameter(cnt - 1) {}
            Dim clsSQL As New MysqlHelper()
            For i As Integer = 0 To cnt - 1
                param.SetValue(clsSQL.CreateInParam(arrItem(i).ParamId, arrItem(i).ParamValue, arrItem(i).Type, arrItem(i).Size), i)
            Next

            Return param
        End Function
    End Class
End Namespace